import pandas as pd
import win32com.client
import xlrd
import os
from openpyxl import load_workbook


def transfer(zz_path):

    excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
    wb = excel.Workbooks.Open(zz_path)

    new_path = r"{old_path}x".format(old_path=zz_path)
    if os.path.exists(new_path):
        os.remove(new_path)
    wb.SaveAs(new_path, FileFormat=51)
    wb.Close()
    excel.Application.Quit()


def zzimport(nowdate, tax_path):
    zz_path = r'D:\税务机器人\{}\总账数据\CUX_科目按来源展示.xls'.format(nowdate)
    transfer(zz_path)
    zzfp = pd.read_excel(r"{old_path}x".format(old_path=zz_path), sheet_name=0, skiprows=4, dtype='str')
    print(zzfp)
    zz_data = zzfp[zzfp['科目编码'].str.contains('2370010501|2370010801')]
    print(zz_data)
    with pd.ExcelWriter(tax_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        book = load_workbook(tax_path)
        writer.book = book  # 读取excelx
        writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # 复制excel的所有表
        zz_data.to_excel(writer, sheet_name='基础表2', startrow=4, index=None)

